package com.xuxueli.commontdemo.util;
import com.alibaba.druid.sql.PagerUtils;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOperator;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor;
import com.alibaba.druid.sql.dialect.oracle.ast.stmt.OracleSelectQueryBlock;
import com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser;
import com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor;
import com.alibaba.druid.sql.parser.ParserException;
import com.alibaba.druid.stat.TableStat;
import com.xuxueli.commontdemo.entity.*;
import com.xuxueli.commontdemo.exception.ErrorException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import javax.annotation.Resource;
import java.util.*;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 测试生成sql
 */
@Slf4j
public class TestDbParaToSelect {

    public static void main(String[] args) {
        ApiSet apiSet = new ApiSet();
        SQLSelectQueryBlock query = new SQLSelectQueryBlock();
        List<ApiResponseParam> selectList = new ArrayList<>();
        ApiResponseParam apiResponseParams = new ApiResponseParam();
        apiResponseParams.setColumnName("id");
        ApiResponseParam apiResponseParamss = new ApiResponseParam();
        apiResponseParamss.setColumnName("user_name");
        selectList.add(apiResponseParams);
        selectList.add(apiResponseParamss);
        apiSet.setSelectList(selectList);
        //拼接select语句
        for (ApiResponseParam apiResponseParam : apiSet.getSelectList()) {
            if (apiResponseParam.getColumnName().equalsIgnoreCase(apiResponseParam.getColumnName())) {
                query.addSelectItem(SQLUtils.toSelectItem(apiResponseParam.getColumnName(), "mysql"));
            }else {
                query.addSelectItem(String.valueOf(SQLUtils.toSelectItem(apiResponseParam.getColumnName(), "mysql")), apiResponseParam.getColumnName());
            }
        }
        // 表，不取别名
        apiSet.setTableName("test");
        query.setFrom(apiSet.getTableName(), "user_test");
        List<OpenApiColumnValueBO> whereList = new ArrayList<>();
        OpenApiColumnValueBO openApiColumnValueBO = new OpenApiColumnValueBO();
        openApiColumnValueBO.setColumnName("user_name");
        openApiColumnValueBO.setColumnType(1);
        openApiColumnValueBO.setExpression("like");
        openApiColumnValueBO.setParamValue("like");
        OpenApiColumnValueBO openApiColumnValueBOs = new OpenApiColumnValueBO();
        openApiColumnValueBOs.setColumnName("user_me");
        openApiColumnValueBOs.setColumnType(1);
        openApiColumnValueBOs.setExpression("=");
        openApiColumnValueBOs.setParamValue("asfas");
        OpenApiColumnValueBO openApiColumnValueBOss = new OpenApiColumnValueBO();
        openApiColumnValueBOss.setColumnName("time_use");
        openApiColumnValueBOss.setColumnType(91);
        openApiColumnValueBOss.setExpression("=");
        openApiColumnValueBOss.setParamValue("2020-03-22");
        OpenApiColumnValueBO openApiColumnValueBOsss = new OpenApiColumnValueBO();
        openApiColumnValueBOsss.setColumnName("passewew");
        openApiColumnValueBOsss.setColumnType(1);
        openApiColumnValueBOsss.setExpression("=");
        openApiColumnValueBOsss.setParamValue("2020-03-22");
        whereList.add(openApiColumnValueBO);
        whereList.add(openApiColumnValueBOs);
        whereList.add(openApiColumnValueBOss);
        whereList.add(openApiColumnValueBOsss);
        apiSet.setWhereList(whereList);
        //where语句
        apiSet.getWhereList().forEach(param -> {
            if (param.getColumnName().equals("pageIndex") || param.getColumnName().equals("pageSize")) {
                return;
            }
            SQLExpr expr = buildPartWhereSql(param, String.valueOf("mysql"));
            query.addWhere(expr);
        });
        String selectSql = SQLUtils.format(SQLUtils.toSQLString(query, "mysql"), "mysql");
        System.out.println("select拼接结果："+selectSql);
        apiSet.setSqlFinal(selectSql);
        List<SortColumn> sortList = new ArrayList<>();
        SortColumn sortColumn = new SortColumn();
        sortColumn.setColumnName("age");
        sortColumn.setOrderType("desc");
        SortColumn sortColumns = new SortColumn();
        sortColumns.setColumnName("time");
        sortColumns.setOrderType("asc");
        sortList.add(sortColumn);
        sortList.add(sortColumns);
        apiSet.setSortList(sortList);
        //sort语句
        String orderSqlFinal = StringUtils.EMPTY;
        if(CollectionUtils.isNotEmpty(apiSet.getSortList())){
            String order = sortList.stream()
                    .filter(m -> StringUtils.isNotBlank(m.getColumnName()) && StringUtils.isNotBlank(m.getOrderType()))
                    .map(m -> " " + m.getColumnName() + " " + m.getOrderType().toUpperCase())
                    .collect(Collectors.joining(","));
            String orderSql = new StringBuffer().append(selectSql).append(" order by ").append(order).toString();
            orderSqlFinal = SQLUtils.format(orderSql, "mysql");
            apiSet.setSqlFinal(orderSqlFinal);
        }
        if(null==apiSet.getPageIndex()){
            apiSet.setPageIndex(1);
        }
        if(null==apiSet.getPageSize()){
            apiSet.setPageSize(100000);
        }
        String limitSql = StringUtils.EMPTY;
        if(apiSet.getExistPage()){
            limitSql = PagerUtils.limit(orderSqlFinal, "mysql", apiSet.getPageIndex(), apiSet.getPageSize());
        }else{
            limitSql = orderSqlFinal;
        }
        System.out.println("最终拼接sql语句："+limitSql);
        apiSet.setSqlFinal(limitSql);
        System.out.println(apiSet);
        ParserSqlVo parserSqlVo = new ParserSqlVo();
        parserSqlVo.setSql(apiSet.getSqlFinal());
        ResponseResult responseResult = parserSql(parserSqlVo);
        System.out.println(responseResult);
        String innerQuerySQL = getInnerQuerySQL(apiSet.getSqlFinal(), apiSet.getWhereList());
        System.out.println(innerQuerySQL);
    }

    protected static SQLExpr buildPartWhereSql(OpenApiColumnValueBO param, String dbType) {
        int dataType = param.getColumnType();
        String nativeVal = String.valueOf(param.getParamValue());
        String columnName = param.getColumnName();
        String expression = param.getExpression();
        return buildWhereExpr(dataType, columnName, nativeVal, expression, dbType);
    }

    protected static SQLExpr buildWhereExpr(int dataType, String columnName, String nativeVal, String expression, String dbType) {
        String valueSQL = convertValue(dataType, nativeVal);
        SQLExpr sqlExpr = null;
        if (SQLBinaryOperator.Like.name.equals(expression.toUpperCase())) { // expression 是 like
            SQLBinaryOpExpr likeExpr = new SQLBinaryOpExpr();
            likeExpr.setLeft(SQLUtils.toSQLExpr(columnName));
            likeExpr.setOperator(SQLBinaryOperator.Like);
            likeExpr.setRight(SQLUtils.toSQLExpr("'%" + (JdbcTypeUtils.isDate(dataType) ? valueSQL : nativeVal) + "%'"));
            sqlExpr = likeExpr;
        } else { // 其他情况直接拼接
            String whereSQL = columnName + " " + expression + " " + valueSQL;
            sqlExpr = SQLUtils.toSQLExpr(whereSQL, "mysql");
        }
        return sqlExpr;
    }

    /**
     * 将日期，数字转换成对应的String
     * @param dataType
     * @param nativeVal
     * @return
     */
    private static String convertValue(int dataType, String nativeVal) {
        String valueSQL = "";
        if (JdbcTypeUtils.isDate(dataType)) {
            Date date = null;
            try {
                date = com.xuxueli.commontdemo.commont.DateUtils.computeAndFormatDate(nativeVal);
                if (null == date) {
                    throw new ErrorException(500,"日期格式有误");
                }
            }catch (Exception ex){
                throw new ErrorException(500,"日期格式有误");
            }
            //valueSQL = getDateSql(date); // getDateSql需要策略类来处理，不整到helper里
            valueSQL = getDateSql(nativeVal); // getDateSql需要策略类来处理，不整到helper里
        } else if (JdbcTypeUtils.isNumber(dataType)) {
            valueSQL = nativeVal;
        } else {
            valueSQL = " '" + nativeVal + "' ";
        }
        return valueSQL;
    }

    protected static String getDateSql(Date date) {
        return new StringBuffer(" date_format('" + DateUtils.format(date, "yyyy-MM-dd HH:mm:ss") + "', '%Y-%m-%d') ").toString();
    }

    protected static String getDateSql(String date) {
        if (date.contains(" ")) {
            if (date.contains("00:00:00")) {
                return " to_date('" + date.split(" ")[0] + "','yyyy-mm-dd')";
            } else {
                return " to_date('" + date.split("\\.")[0] + "','yyyy-mm-dd hh24:mi:ss')";
            }
        } else {
            return " to_date('" + date + "','yyyy-mm-dd')";
        }
    }


    /**
     * SQL解析
     * 该方法从SQL语句中解析出 1)入参，入参的操作符  2)出参，出参的函数，出参的别名alias
     * 以及拼接listColumn接口获取的表字段
     */
    public static ResponseResult parserSql(ParserSqlVo parsersqlVo) {
        parsersqlVo.setSql("SELECT id, user_name\n" +
                "FROM test user_test\n" +
                "WHERE user_name LIKE '%like%'\n" +
                "\tAND user_me = 'asfas'\n" +
                "\tAND time_use = date_format('2020-03-22 00:00:00', '%Y-%m-%d')\n" +
                "&& passewew=#passewew# &&\n"+
                "ORDER BY age DESC, time ASC");
        if (parsersqlVo == null || StringUtils.isEmpty(parsersqlVo.getSql())) {
            return ResponseResult.error(Integer.parseInt(OpenApiStatusCodeEnum.API_SQL_NOT_SUPPORT_COUNT.getStatusCode()),OpenApiStatusCodeEnum.API_SQL_NOT_SUPPORT_COUNT.getMessage());
        }

        String sql = parsersqlVo.getSql();

        String baseSql = null;
        baseSql = sql;
        List<String> requestParamList = new ArrayList<>(); // 入参
        List<String> responseParamList = new ArrayList<>(); // 出参

        // 去掉注释
        Matcher annotationMatcher = Pattern.compile("(--).*?(?=(\\n|$))").matcher(sql);
        while (annotationMatcher.find()) {
            sql = sql.replace(annotationMatcher.group(), "");
            baseSql = sql;
        }

        // 解析入参
        Matcher m = Pattern.compile("#(.*?)#").matcher(baseSql);
        AtomicBoolean isDictionary = new AtomicBoolean(false);
        while (m.find()) {
            isDictionary.set(false);
            Arrays.asList(Dictionary.DICTIONARY_LIST).forEach(dictionary -> {
                if (m.group().contains(dictionary)) {
                    isDictionary.set(true);
                }
            });
            if(isDictionary.get()){
                continue;
            }
            if (m.group().contains("(") && m.group().contains(")")) {
                if (!requestParamList.contains(StringUtils.substringBetween(m.group(), "#(", ")#").trim())) {
                    requestParamList.add(StringUtils.substringBetween(m.group(), "#(", ")#").trim());
                }
                sql = sql.replace(m.group(), "(1,2,3,4)");
            } else if (baseSql.split(m.group())[0].trim().toLowerCase().endsWith(" like")) {
                Matcher m1 = Pattern.compile("'%(.*?)%'").matcher(m.group());
                Matcher m2 = Pattern.compile("'%(.*?)'").matcher(m.group());
                Matcher m3 = Pattern.compile("'(.*?)%'").matcher(m.group());
                if (m1.find()) {
                    if (!requestParamList.contains(StringUtils.substringBetween(m.group(), "#'%", "%'#").trim())) {
                        requestParamList.add(StringUtils.substringBetween(m.group(), "#'%", "%'#").trim());
                        //baseSql = sql.replaceAll(m.group(), StringUtils.substringBetween(m.group(), "#'%", "%'#"));
                    }
                    sql = sql.replace(m.group(), StringUtils.substringBetween(m.group(), "#'%", "%'#").trim());
                } else if (m2.find()) {
                    if (!requestParamList.contains(StringUtils.substringBetween(m.group(), "#'%", "'#").trim())) {
                        requestParamList.add(StringUtils.substringBetween(m.group(), "#'%", "'#").trim());
                        // baseSql = sql.replaceAll(m.group(), StringUtils.substringBetween(m.group(), "#'%", "'#"));
                    }
                    sql = sql.replaceAll(m.group(), StringUtils.substringBetween(m.group(), "#'%", "'#").trim());
                } else if (m3.find()) {
                    if (!requestParamList.contains(StringUtils.substringBetween(m.group(), "#'", "%'#").trim())) {
                        requestParamList.add(StringUtils.substringBetween(m.group(), "#'", "%'#").trim());
                        //   baseSql = sql.replaceAll(m.group(), StringUtils.substringBetween(m.group(), "#'", "%'#"));
                    }
                    sql = sql.replaceAll(m.group(), StringUtils.substringBetween(m.group(), "#'", "%'#").trim());
                }
            } else {
                if (!requestParamList.contains(StringUtils.substringBetween(m.group(), "#", "#").trim())) {
                    requestParamList.add(StringUtils.substringBetween(m.group(), "#", "#").trim());
                    //  baseSql = sql.replaceAll(m.group(), StringUtils.substringBetween(m.group(), "#", "#"));
                }
                sql = sql.replaceAll(m.group(), StringUtils.substringBetween(m.group(), "#", "#").trim());
            }
        }

        List<String> maybeEmptySql = new ArrayList<>();
        // 去掉空参语句
        Matcher emptyParamMatcher = Pattern.compile("&&(.*?)&&").matcher(sql);
        while (emptyParamMatcher.find()) {
            maybeEmptySql.add(emptyParamMatcher.group());
            sql = StringUtils.replaceOnce(sql, emptyParamMatcher.group(), "1 = 1");
        }

        // 解析出参
        try {
            MySqlStatementParser parser = new MySqlStatementParser(sql);
            SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) parser.parseSelect();
            MySqlSchemaStatVisitor mySqlSchemaStatVisitor = new MySqlSchemaStatVisitor();
            sqlSelectStatement.accept(mySqlSchemaStatVisitor);
            SQLSelect select = sqlSelectStatement.getSelect();
            SQLSelectQueryBlock queryBlock = select.getQueryBlock();
            if (queryBlock != null) {
                List<SQLSelectItem> selectList = queryBlock.getSelectList();
                selectList.forEach(sqlSelectItem -> {
                    responseParamList.add(sqlSelectItem.getExpr().toString());
                });
            } else {
                if (StringUtils.containsIgnoreCase(sql, "union")) {
                    SQLUnionQuery unionQuery = (SQLUnionQuery) select.getFirstQueryBlock().getParent();
                    List<SQLSelectQuery> relations = unionQuery.getRelations();
                    relations.forEach(query -> {
                        ((MySqlSelectQueryBlock) query).getSelectList().forEach(
                                sqlSelectItem -> {
                                    if (sqlSelectItem.getAlias() != null) {
                                        responseParamList.add(sqlSelectItem.getAlias().toString());
                                    } else {
                                        responseParamList.add(sqlSelectItem.getExpr().toString());
                                    }
                                }
                        );
                    });
                } else {
                    //在解析union语句中selectList是Null，必须从mySqlSchemaStatVisitor中取
                    Collection<TableStat.Column> columns = mySqlSchemaStatVisitor.getColumns();
                    columns.forEach(item -> {
                        if (item.isSelect()) {
                            responseParamList.add(item.getName());
                        }
                    });
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return ResponseResult.error(Integer.parseInt(OpenApiStatusCodeEnum.API_SQL_NOT_SUPPORT_COUNT.getStatusCode()),"SQL解析失败，SQL语句不合法," + e.getMessage());
        }

        Map<String, Object> dataMap = new HashMap();
        dataMap.put("requestParam", new SQLParserUtils().assembleRequestParam(requestParamList, maybeEmptySql));
        dataMap.put("responseParam", new SQLParserUtils().assembleResponseParam(responseParamList));
        return ResponseResult.ok("SQL解析成功").putData(dataMap);
    }

    /**
     * 手写sql解析
     */
    protected static String getInnerQuerySQLOra(String baseSql, List<OpenApiColumnValueBO> params) {
        // 去掉注释
        Matcher annotationMatcher = Pattern.compile("(--).*?(?=(\\n|$))").matcher(baseSql);
        while (annotationMatcher.find()) {
            baseSql = baseSql.replace(annotationMatcher.group(), "");
        }

        // 处理空参
        Matcher EmptyParamMatcher = Pattern.compile("&&(.*?)&&").matcher(baseSql);
        List<String> maybeEmptyParamSqlList = new ArrayList<>();
        while (EmptyParamMatcher.find()) {
            maybeEmptyParamSqlList.add(EmptyParamMatcher.group());
        }
        int index;
        for (String maybeEmptyParamSql : maybeEmptyParamSqlList) {
            if (null == params) {
                baseSql = StringUtils.replaceOnce(baseSql, maybeEmptyParamSql, "");
                break;
            }
            index = 0;
            for (OpenApiColumnValueBO item : params) {
                if (maybeEmptyParamSql.contains("(") && maybeEmptyParamSql.contains(")")){
                    if (StringUtils.substringBetween(maybeEmptyParamSql,"(",")").trim().equalsIgnoreCase(item.getColumnName())) {
                        baseSql = StringUtils.replaceOnce(baseSql, maybeEmptyParamSql, StringUtils.substringBetween(maybeEmptyParamSql, "&&", "&&"));
                        break;
                    } else {
                        index++;
                    }
                }else if (maybeEmptyParamSql.contains("'%") && maybeEmptyParamSql.contains("%'")){
                    if (StringUtils.substringBetween(maybeEmptyParamSql,"'%","%'").trim().equalsIgnoreCase(item.getColumnName())) {
                        baseSql = StringUtils.replaceOnce(baseSql, maybeEmptyParamSql, StringUtils.substringBetween(maybeEmptyParamSql, "&&", "&&"));
                        break;
                    } else {
                        index++;
                    }
                }else if (maybeEmptyParamSql.contains("'%") && !maybeEmptyParamSql.contains("%'")){
                    if (StringUtils.substringBetween(maybeEmptyParamSql,"'%","'").trim().equalsIgnoreCase(item.getColumnName())) {
                        baseSql = StringUtils.replaceOnce(baseSql, maybeEmptyParamSql, StringUtils.substringBetween(maybeEmptyParamSql, "&&", "&&"));
                        break;
                    } else {
                        index++;
                    }
                }else  if (!maybeEmptyParamSql.contains("'%") && maybeEmptyParamSql.contains("%'")){
                    if (StringUtils.substringBetween(maybeEmptyParamSql,"'","%'").trim().equalsIgnoreCase(item.getColumnName())) {
                        baseSql = StringUtils.replaceOnce(baseSql, maybeEmptyParamSql, StringUtils.substringBetween(maybeEmptyParamSql, "&&", "&&"));
                        break;
                    } else {
                        index++;
                    }
                }else {
                    if (maybeEmptyParamSql.split("=")[0].trim().substring(2).trim().equalsIgnoreCase(item.getColumnName())) {
                        baseSql = StringUtils.replaceOnce(baseSql, maybeEmptyParamSql, StringUtils.substringBetween(maybeEmptyParamSql, "&&", "&&"));
                        break;
                    } else {
                        index++;
                    }
                }
            }
//            if (index == params.size()) {
//                baseSql = StringUtils.replaceOnce(baseSql, maybeEmptyParamSql, "1 = 1");
//            }
        }

        // 替换入参
        Matcher m = Pattern.compile("#(.*?)#").matcher(baseSql);
        Map paramsMap = new HashMap<>();
        params.forEach( i-> {
/*            if (null != DatePattern.getPatternByDateStr(String.valueOf(i.getParamValue()))){
                paramsMap.put(i.getColumnName(),getDateSql(String.valueOf(i.getParamValue())));
            }else {
                paramsMap.put(i.getColumnName(), i.getParamValue());
            }*/
            paramsMap.put(i.getColumnName(), i.getParamValue());
        });
        int i = 0;
        while (m.find()) {
            //   baseSql = dealDictionary(baseSql, m.group());
//            System.out.println("baseSql = " + baseSql);
            if (m.group().contains("(") && m.group().contains(")")) {
                baseSql = StringUtils.replaceOnce(baseSql, m.group(), "(" + paramsMap.get(m.group().substring(2,m.group().length()-2).trim()) + ") ");
            } else if (baseSql.split(m.group())[0].trim().toLowerCase().endsWith(" like")) {
                Matcher m1 = Pattern.compile("'%(.*?)%'").matcher(m.group());
                Matcher m2 = Pattern.compile("'%(.*?)'").matcher(m.group());
                Matcher m3 = Pattern.compile("'(.*?)%'").matcher(m.group());
                if (m1.find()) {
                    baseSql = StringUtils.replaceAll(baseSql, m.group(), "'%" + paramsMap.get(m.group().substring(3,m.group().length()-3).trim()) + "%'");
                } else if (m2.find()) {
                    baseSql = StringUtils.replaceAll(baseSql, m.group(), "'%" + paramsMap.get(m.group().substring(3,m.group().length()-2).trim()) + "'");
                } else if (m3.find()) {
                    baseSql = StringUtils.replaceAll(baseSql, m.group(), "'" + paramsMap.get(m.group().substring(2,m.group().length()-3).trim()) + "%'");
                }
            } else {
                //if (paramsMap.get(m.group().substring(1,m.group().length()-1).trim()).toString().contains("table")){
                if (m.group().substring(1,m.group().length()-1).trim().contains("table")){
                    log.info("contain table");
                    baseSql = StringUtils.replaceAll(baseSql, m.group(), "" + paramsMap.get(m.group().substring(1,m.group().length()-1).trim()) + "");

                }else {
                    log.info("not contain table");
                    String key = m.group().substring(1, m.group().length() - 1).trim();
                    if(paramsMap.containsKey(key)) {
                        String orElse = Optional.ofNullable(paramsMap.get(key)).orElse("").toString();
                        //截取非必填项为null
                        if (StringUtils.isBlank(orElse) || "null".equals(orElse)) {
                            String[] strings = baseSql.split(m.group());
                            String startStr = strings[0];
                            String endStr = StringUtils.EMPTY;
                            if (null != strings && strings.length > 1) {
                                if(startStr.contains("to_date(")){
                                    if(strings[1].indexOf("and")>0){
                                        endStr = strings[1].substring(strings[1].indexOf("and"));
                                    }else{
                                        endStr = "";
                                    }
                                }else{
                                    endStr = strings[1];
                                }
                            }
                            String toString = new StringBuilder(startStr).reverse().toString();
                            toString = toString.substring(toString.indexOf("dna") + 3);
                            String toStrings = new StringBuilder(toString).reverse().toString();
                            System.out.println("截取" + toStrings + endStr);
                            baseSql = toStrings + endStr;
                        } else {
                            baseSql = StringUtils.replaceAll(baseSql, m.group(), "'" + paramsMap.get(m.group().substring(1, m.group().length() - 1).trim()) + "'");
                        }
                    }
                }

                /*if (baseSql.contains(m.group())) {
                 *//*                    if (paramsMap.get(m.group().substring(1,m.group().length()-1).trim()).toString().contains("to_date(")){
                        baseSql = StringUtils.replaceAll(baseSql, m.group(), "" + paramsMap.get(m.group().substring(1,m.group().length()-1).trim()) + "");

                    }else {
                        baseSql = StringUtils.replaceAll(baseSql, m.group(), "'" + paramsMap.get(m.group().substring(1,m.group().length()-1).trim()) + "'");
                    }*//*
                    baseSql = StringUtils.replaceAll(baseSql, m.group(), "'" + paramsMap.get(m.group().substring(1,m.group().length()-1).trim()) + "'");

                }*/
            }
        }
        if (baseSql.contains("&&")){
            baseSql = StringUtils.replaceAll(baseSql,"&&", "");
        }
        return baseSql;
    }

    /**
     * 手写sql解析
     */
    protected static String getInnerQuerySQL(String baseSql, List<OpenApiColumnValueBO> params) {
        // 去掉注释
        Matcher annotationMatcher = Pattern.compile("(--).*?(?=(\\n|$))").matcher(baseSql);
        while (annotationMatcher.find()) {
            baseSql = baseSql.replace(annotationMatcher.group(), "");
        }

        // 处理空参
        Matcher EmptyParamMatcher = Pattern.compile("&&(.*?)&&").matcher(baseSql);
        List<String> maybeEmptyParamSqlList = new ArrayList<>();
        while (EmptyParamMatcher.find()) {
            maybeEmptyParamSqlList.add(EmptyParamMatcher.group());
        }
        int index;
        for (String maybeEmptyParamSql : maybeEmptyParamSqlList) {
            if (null == params) {
                baseSql = StringUtils.replaceOnce(baseSql, maybeEmptyParamSql, "");
                break;
            }
            index = 0;
            for (OpenApiColumnValueBO item : params) {
                if (maybeEmptyParamSql.contains("(") && maybeEmptyParamSql.contains(")")){
                    if (StringUtils.substringBetween(maybeEmptyParamSql,"(",")").trim().equalsIgnoreCase(item.getColumnName())) {
                        baseSql = StringUtils.replaceOnce(baseSql, maybeEmptyParamSql, StringUtils.substringBetween(maybeEmptyParamSql, "&&", "&&"));
                        break;
                    } else {
                        index++;
                    }
                }else if (maybeEmptyParamSql.contains("'%") && maybeEmptyParamSql.contains("%'")){
                    if (StringUtils.substringBetween(maybeEmptyParamSql,"'%","%'").trim().equalsIgnoreCase(item.getColumnName())) {
                        baseSql = StringUtils.replaceOnce(baseSql, maybeEmptyParamSql, StringUtils.substringBetween(maybeEmptyParamSql, "&&", "&&"));
                        break;
                    } else {
                        index++;
                    }
                }else if (maybeEmptyParamSql.contains("'%") && !maybeEmptyParamSql.contains("%'")){
                    if (StringUtils.substringBetween(maybeEmptyParamSql,"'%","'").trim().equalsIgnoreCase(item.getColumnName())) {
                        baseSql = StringUtils.replaceOnce(baseSql, maybeEmptyParamSql, StringUtils.substringBetween(maybeEmptyParamSql, "&&", "&&"));
                        break;
                    } else {
                        index++;
                    }
                }else  if (!maybeEmptyParamSql.contains("'%") && maybeEmptyParamSql.contains("%'")){
                    if (StringUtils.substringBetween(maybeEmptyParamSql,"'","%'").trim().equalsIgnoreCase(item.getColumnName())) {
                        baseSql = StringUtils.replaceOnce(baseSql, maybeEmptyParamSql, StringUtils.substringBetween(maybeEmptyParamSql, "&&", "&&"));
                        break;
                    } else {
                        index++;
                    }
                }else {
                    if (maybeEmptyParamSql.split("=")[0].trim().substring(2).trim().equalsIgnoreCase(item.getColumnName())) {
                        baseSql = StringUtils.replaceOnce(baseSql, maybeEmptyParamSql, StringUtils.substringBetween(maybeEmptyParamSql, "&&", "&&"));
                        break;
                    } else {
                        index++;
                    }
                }
            }
            if (index == params.size()) {
                baseSql = StringUtils.replaceOnce(baseSql, maybeEmptyParamSql, "1 = 1");
            }
        }

        // 替换入参
        Matcher m = Pattern.compile("#(.*?)#").matcher(baseSql);
        Map paramsMap = new HashMap<>();
        params.forEach( i-> {
           /* if (null != DatePattern.getPatternByDateStr(String.valueOf(i.getParamValue()))){
                paramsMap.put(i.getColumnName(),getDateSql(DateUtils.computeAndFormatDate(String.valueOf(i.getParamValue())),String.valueOf(i.getParamValue())));
            }else {
                paramsMap.put(i.getColumnName(), i.getParamValue());
            }*/
            paramsMap.put(i.getColumnName(), i.getParamValue());
        });
        int i = 0;
        while (m.find()) {
            //        baseSql = dealDictionary(baseSql, m.group());
//            System.out.println("baseSql = " + baseSql);
            if (m.group().contains("(") && m.group().contains(")")) {
                baseSql = StringUtils.replaceOnce(baseSql, m.group(), "(" + paramsMap.get(m.group().substring(2,m.group().length()-2).trim()) + ") ");
            } else if (baseSql.split(m.group())[0].trim().toLowerCase().endsWith(" like")) {
                Matcher m1 = Pattern.compile("'%(.*?)%'").matcher(m.group());
                Matcher m2 = Pattern.compile("'%(.*?)'").matcher(m.group());
                Matcher m3 = Pattern.compile("'(.*?)%'").matcher(m.group());
                if (m1.find()) {
                    baseSql = StringUtils.replaceAll(baseSql, m.group(), "'%" + paramsMap.get(m.group().substring(3,m.group().length()-3).trim()) + "%'");
                } else if (m2.find()) {
                    baseSql = StringUtils.replaceAll(baseSql, m.group(), "'%" + paramsMap.get(m.group().substring(3,m.group().length()-2).trim()) + "'");
                } else if (m3.find()) {
                    baseSql = StringUtils.replaceAll(baseSql, m.group(), "'" + paramsMap.get(m.group().substring(2,m.group().length()-3).trim()) + "%'");
                }
            } else {
//                if (baseSql.contains(m.group())) {
                if (paramsMap.get(m.group().substring(1,m.group().length()-1).trim()).toString().contains("table")){
                    log.info("包含table，做出处理");
                    baseSql = StringUtils.replaceAll(baseSql, m.group(), "" + paramsMap.get(m.group().substring(1,m.group().length()-1).trim()) + "");

                }else {
                    log.info("不包含table，不用做出处理");
                    baseSql = StringUtils.replaceAll(baseSql, m.group(), "'" + paramsMap.get(m.group().substring(1,m.group().length()-1).trim()) + "'");
                }
//                }

//                baseSql = StringUtils.replaceAll(baseSql, m.group(), "'" + paramsMap.get(m.group().substring(1,m.group().length()-1).trim()) + "'");
            }
        }
        return baseSql;
    }

    /**
     * SQL解析
     * 该方法从SQL语句中解析出 1)入参，入参的操作符  2)出参，出参的函数，出参的别名alias
     * 以及拼接listColumn接口获取的表字段
     */
    public ResponseResult parserSqls(ParserSqlVo parsersqlVo) throws ParserException {
        if (parsersqlVo == null || StringUtils.isEmpty(parsersqlVo.getSql())) {
            return ResponseResult.error(Integer.parseInt(OpenApiStatusCodeEnum.API_SQL_NOT_SUPPORT_COUNT.getStatusCode()),OpenApiStatusCodeEnum.API_SQL_NOT_SUPPORT_COUNT.getMessage());
        }

        String sql = parsersqlVo.getSql();

        String baseSql = null;
        baseSql = sql;
        List<String> requestParamList = new ArrayList<>(); // 入参
        List<String> responseParamList = new ArrayList<>(); // 出参

        // 去掉注释
        Matcher annotationMatcher = Pattern.compile("(--).*?(?=(\\n|$))").matcher(sql);
        while (annotationMatcher.find()) {
            sql = sql.replace(annotationMatcher.group(), "");
            baseSql = sql;
        }

        // 解析入参
        Matcher m = Pattern.compile("#(.*?)#").matcher(baseSql);
        AtomicBoolean isDictionary = new AtomicBoolean(false);
        while (m.find()) {
            if (m.group().contains("(") && m.group().contains(")")) {
                if (!requestParamList.contains(StringUtils.substringBetween(m.group(), "#(", ")#").trim())) {
                    requestParamList.add(StringUtils.substringBetween(m.group(), "#(", ")#").trim());
                }
                sql = sql.replace(m.group(), "("+StringUtils.substringBetween(m.group(), "#(", ")#").trim()+")");
            } else if (baseSql.split(m.group())[0].trim().toLowerCase().endsWith(" like")) {
                Matcher m1 = Pattern.compile("'%(.*?)%'").matcher(m.group());
                Matcher m2 = Pattern.compile("'%(.*?)'").matcher(m.group());
                Matcher m3 = Pattern.compile("'(.*?)%'").matcher(m.group());
                if (m1.find()) {
                    if (!requestParamList.contains(StringUtils.substringBetween(m.group(), "#'%", "%'#").trim())) {
                        requestParamList.add(StringUtils.substringBetween(m.group(), "#'%", "%'#").trim());
                        //baseSql = sql.replaceAll(m.group(), StringUtils.substringBetween(m.group(), "#'%", "%'#"));
                    }
                    sql = sql.replace(m.group(), StringUtils.substringBetween(m.group(), "#'%", "%'#").trim());
                } else if (m2.find()) {
                    if (!requestParamList.contains(StringUtils.substringBetween(m.group(), "#'%", "'#").trim())) {
                        requestParamList.add(StringUtils.substringBetween(m.group(), "#'%", "'#").trim());
                        // baseSql = sql.replaceAll(m.group(), StringUtils.substringBetween(m.group(), "#'%", "'#"));
                    }
                    sql = sql.replaceAll(m.group(), StringUtils.substringBetween(m.group(), "#'%", "'#").trim());
                } else if (m3.find()) {
                    if (!requestParamList.contains(StringUtils.substringBetween(m.group(), "#'", "%'#").trim())) {
                        requestParamList.add(StringUtils.substringBetween(m.group(), "#'", "%'#").trim());
                        //   baseSql = sql.replaceAll(m.group(), StringUtils.substringBetween(m.group(), "#'", "%'#"));
                    }
                    sql = sql.replaceAll(m.group(), StringUtils.substringBetween(m.group(), "#'", "%'#").trim());
                }
            } else {
                if (!requestParamList.contains(StringUtils.substringBetween(m.group(), "#", "#").trim())) {
                    requestParamList.add(StringUtils.substringBetween(m.group(), "#", "#").trim());
                    //  baseSql = sql.replaceAll(m.group(), StringUtils.substringBetween(m.group(), "#", "#"));
                }
                sql = sql.replaceAll(m.group(), StringUtils.substringBetween(m.group(), "#", "#").trim());
            }
        }

        Map<String,String> requestAliasMap = new HashMap<>();
        List<String> maybeEmptySql = new ArrayList<>();
        // 去掉空参语句
        Matcher emptyParamMatcher = Pattern.compile("&&(.*?)&&").matcher(sql);
        while (emptyParamMatcher.find()) {
            String responseAlias = "";
            if (emptyParamMatcher.group().contains(" = ")){
                responseAlias = StringUtils.substringBetween(emptyParamMatcher.group(), "=", "&&").trim();
                requestAliasMap.put(responseAlias,StringUtils.substringBetween(emptyParamMatcher.group(), "&&", "=").trim());
            }

            if (emptyParamMatcher.group().contains(" != ")){
                responseAlias = StringUtils.substringBetween(emptyParamMatcher.group(), "!=", "&&").trim();
                requestAliasMap.put(responseAlias,StringUtils.substringBetween(emptyParamMatcher.group(), "&&", "!=").trim());
            }

            if (emptyParamMatcher.group().contains(" >= ") || emptyParamMatcher.group().contains(">=")){
                responseAlias = StringUtils.substringBetween(emptyParamMatcher.group(), ">=", "&&").trim();
                requestAliasMap.put(responseAlias,StringUtils.substringBetween(emptyParamMatcher.group(), "&&", ">=").trim());
            }

            if (emptyParamMatcher.group().contains(" > ")){
                responseAlias = StringUtils.substringBetween(emptyParamMatcher.group(), ">", "&&").trim();
                requestAliasMap.put(responseAlias,StringUtils.substringBetween(emptyParamMatcher.group(), "&&", ">").trim());
            }

            if (emptyParamMatcher.group().contains(" <= ") || emptyParamMatcher.group().contains("<=")){
                responseAlias = StringUtils.substringBetween(emptyParamMatcher.group(), "<=", "&&").trim();
                requestAliasMap.put(responseAlias,StringUtils.substringBetween(emptyParamMatcher.group(), "&&", "<=").trim());
            }

            if (emptyParamMatcher.group().contains(" < ")){
                responseAlias = StringUtils.substringBetween(emptyParamMatcher.group(), "<", "&&").trim();
                requestAliasMap.put(responseAlias,StringUtils.substringBetween(emptyParamMatcher.group(), "&&", "<").trim());
            }

            if (emptyParamMatcher.group().contains(" like ")){
                responseAlias = StringUtils.substringBetween(emptyParamMatcher.group(), " like ", "&&").trim();
                requestAliasMap.put(responseAlias,StringUtils.substringBetween(emptyParamMatcher.group(), "&&", " like ").trim());
            }
            if (emptyParamMatcher.group().contains(" in ")){
                responseAlias = StringUtils.substringBetween(emptyParamMatcher.group(), "(", ")");
                requestAliasMap.put(responseAlias.trim(),StringUtils.substringBetween(emptyParamMatcher.group(), "&&", " in ").trim());
            }
            maybeEmptySql.add(responseAlias);
            sql = StringUtils.replaceOnce(sql, emptyParamMatcher.group(), "1 = 1");
        }
        Map<String,String> responseAliasMap = new HashMap<>();

        // 解析出参
        try {
            OracleStatementParser parser = new OracleStatementParser(sql);
            SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) parser.parseSelect();
            OracleSchemaStatVisitor oracleSchemaStatVisitor = new OracleSchemaStatVisitor();
            sqlSelectStatement.accept(oracleSchemaStatVisitor);
            SQLSelect select = sqlSelectStatement.getSelect();
            SQLSelectQueryBlock queryBlock = select.getQueryBlock();
            if (queryBlock != null) {
                List<SQLSelectItem> selectList = queryBlock.getSelectList();
                selectList.forEach(sqlSelectItem -> {
                    if (sqlSelectItem.getAlias() != null) {
                        responseParamList.add(sqlSelectItem.getAlias().toString());
                        responseAliasMap.put(sqlSelectItem.getAlias(),sqlSelectItem.getExpr().toString());
                    } else {
                        responseParamList.add(sqlSelectItem.getExpr().toString());
                    }
                });
            } else {

                if (StringUtils.containsIgnoreCase(sql, "union")) {
                    SQLUnionQuery unionQuery = (SQLUnionQuery) select.getFirstQueryBlock().getParent();
                    List<SQLSelectQuery> relations = unionQuery.getRelations();
                    relations.forEach(query -> {
                        ((OracleSelectQueryBlock) query).getSelectList().forEach(
                                sqlSelectItem -> {
                                    if (sqlSelectItem.getAlias() != null) {
                                        responseParamList.add(sqlSelectItem.getAlias().toString());
                                        responseAliasMap.put(sqlSelectItem.getAlias(),sqlSelectItem.getExpr().toString());
                                    } else {
                                        responseParamList.add(sqlSelectItem.getExpr().toString());
                                    }
                                }
                        );
                    });
                } else {
                    //在解析union语句中selectList是Null，必须从mySqlSchemaStatVisitor中取
                    Collection<TableStat.Column> columns = oracleSchemaStatVisitor.getColumns();
                    columns.forEach(item -> {
                        if (item.isSelect()) {
                            responseParamList.add(item.getName());
                        }
                    });
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            return ResponseResult.error(Integer.parseInt(OpenApiStatusCodeEnum.API_SQL_NOT_SUPPORT_COUNT.getStatusCode()),"SQL解析失败，SQL语句不合法," + e.getMessage());
        }


        Map<String, Object> dataMap = new HashMap();
        dataMap.put("requestParam", new SQLParserUtils().assembleRequestParams(requestParamList, maybeEmptySql, requestAliasMap));
        dataMap.put("responseParam", new SQLParserUtils().assembleResponseParams(responseParamList,responseAliasMap));
        return ResponseResult.ok("SQL解析成功").putData(dataMap);
    }


}
